<?php
require_once("../../config.php");
require_once('../../lib/excel/Worksheet.php');
require_once('../../lib/excel/Workbook.php');
require_once('lib/mylib.php');

global $SESSION;
//ttt du formulaire.
 //print_object($SESSION->connexion_tracking_request);
$form = $SESSION->connexion_tracking_request;

//On détermine le contexte
$contextGlobal = get_context_instance(CONTEXT_SYSTEM, $COURSE->id);
$contexCourse = get_context_instance(CONTEXT_COURSE, $COURSE->id);
$contextBloc = get_context_instance(CONTEXT_BLOCK, $SESSION->connexiontrackinginstanceid);
	
	
//On détermine si l'utilisateur courant a le droit d'utiliser ce scritp de génération de traces. On vérifie à différents niveaux car il peut y avoir des dérogations.
if(has_capability('block/connexion_tracking:viewtracks', $contextGlobal) || has_capability('block/connexion_tracking:viewtracks', $contextBloc) || has_capability('block/connexion_tracking:viewtracks', $contexCourse)){	
	
	$workbook = new Workbook("-");
	//Variables excel
	//On défini les styles
	//Style pour la première ligne
	$formatentete =& $workbook->add_format();
	$formatentete->set_align('center');
	$formatentete->set_color('magenta');
	
	//Style des lignes
	$formatentree =& $workbook->add_format();
	$formatentree->set_align('center');
	$formatentree->set_color('black');
	
	//Style du titre
	$formattitre =& $workbook->add_format();
	$formattitre->set_align('center');
	$formattitre->set_color('red');
	$formattitre->set_bold(1);
	
	// Creating the first worksheet
	$worksheet1 =& $workbook->add_worksheet(get_string("premiere", block_connexion_tracking));
	// set the column width
	$worksheet1->set_column(0, 0, 40);
	$worksheet1->set_column(0, 1, 40);
	$worksheet1->set_column(0, 2, 25);
	$worksheet1->set_column(0, 3, 25);
	$worksheet1->set_column(0, 4, 40);
	// set the row height
	$worksheet1->set_row(1, 20);
	
	//Constuction de la requete sql:
	//Moodle a besoin d'avoir un indentifiant unique sur la premiere colonne id. ici on prend connexion_tracking.id.
	//Si on avait pris user.id, toutes les connexions se serait écrasées au profit de la dernière par utilisateur et num_row = nb users.
	$sub_cat = get_cascade_cat($form->category);
	if(count($sub_cat) == 0){//Cette catégorie est une feuille.
		$sql = 'SELECT id as connecid, userid, start, stop , length, requests, catid'
			. " FROM {$CFG->prefix}connexion_tracking"
			. ' WHERE 1 = 1';//Pour pourvoir utiliser que de AND 
		
		if($form->etudiants != 0){
			$sql .= " AND userid = {$form->etudiants} ";
		}if($form->category != 0){
			$sql .= " AND catid = {$form->category} ";
		}//Dans le cas ou les dates sont utilisee, on cree une variable qui la les stockees pour l affichage.
		if($form->connecstartenabled && $form->connecendenabled){
			$sql .= " AND start > {$form->connecstartdate} AND stop < {$form->connecenddate} ";
			$start = getdate($form->connecstartdate);
			$stop = getdate($form->connecenddate);
			$dateDebut = sprintf("%02d-%02d-%04d", $start[mday], $start[mon], $start[year]);
			$dateFin = sprintf("%02d-%02d-%04d", $stop[mday], $stop[mon], $stop[year]);
			$dates = "-".$dateDebut."--".$dateFin;
		}elseif($form->connecstartenabled){
			$sql .= " AND start > {$form->connecstartdate} ";
			$start = getdate($form->connecstartdate);
			$dateDebut = sprintf("%02d-%02d-%04d", $start[mday], $start[mon], $start[year]);
			$dates = "-".get_string('depuisf', 'block_connexion_tracking')."-".$dateDebut."--";
		}elseif($form->connecendenabled){
			$sql .= " AND stop < {$form->connecenddate} ";
			$stop = getdate($form->connecenddate);
			$dateFin = sprintf("%02d-%02d-%04d", $stop[mday], $stop[mon], $stop[year]);
			$dates = "-".get_string('jusquaf', 'block_connexion_tracking')."-".$dateFin;
		}
	}else{//Cette catégorie a des catégories filles, il nous faut lister les connexions correspondantes.
		$listefilles = get_cascade_cat($form->category);
		$sql = 'SELECT DISTINCT id AS connecid, userid,'
			. ' start ,'
			. ' stop , length, requests, catid'
			. ' FROM mdl_connexion_tracking'
			. " WHERE (catid = {$form->category}";
			
		foreach($listefilles as $cat){
			$sql .= " OR catid = $cat ";
		}
		$sql.= " ) ";
			if($form->etudiants != 0){
			$sql .= " AND userid = {$form->etudiants} ";
		}
		if($form->connecstartenabled && $form->connecendenabled){
			$sql .= " AND start > {$form->connecstartdate} AND stop < {$form->connecenddate} ";
			$start = getdate($form->connecstartdate);
			$stop = getdate($form->connecenddate);
			$dateDebut = sprintf("%02d-%02d-%04d", $start[mday], $start[mon], $start[year]);
			$dateFin = sprintf("%02d-%02d-%04d", $stop[mday], $stop[mon], $stop[year]);
			$dates = "-".$dateDebut."--".$dateFin;
		}elseif($form->connecstartenabled){
			$sql .= " AND start > {$form->connecstartdate} ";
			$start = getdate($form->connecstartdate);
			$dateDebut = sprintf("%02d-%02d-%04d", $start[mday], $start[mon], $start[year]);
			$dates = "-".get_string('depuisf', 'block_connexion_tracking')."-".$dateDebut."--";
		}elseif($form->connecendenabled){
			$sql .= " AND stop < {$form->connecenddate} ";
			$stop = getdate($form->connecenddate);
			$dateFin = sprintf("%02d-%02d-%04d", $stop[mday], $stop[mon], $stop[year]);
			$dates = "-".get_string('jusquaf', 'block_connexion_tracking')."-".$dateFin;
		}
	}	
	
	$sql .= ' ORDER BY userid ASC, start ASC'; 	
	//echo $sql;
	
	//Requete
	$connex = get_records_sql($sql);

	//Nous allons maintenant écrire le fichier excel.
	$currentStudent = 0; //Contient l'id de l'étudiant courant / précédent.
	if(($form->etudiants == 0) && ($form->category == 0)){//Cas ou on liste toutes les connexions de tous les etudiants pour toutes les catégories.
		$nbrequetestotal = 0;
		$dureetotale = 0;
		$nbrequetes = 0;
		$userinfo = get_etu_names($form->etudiants);
		$catname = get_cat_name($form->category);
		$worksheet1->write_string(0, 1, get_string("toute", "block_connexion_tracking"), $formattitre);
		HeaderingExcel(get_string("complet", "block_connexion_tracking").$dates.'.xls');
		foreach ($connex as $connec) {
			if(($currentStudent == 0) || ($currentStudent != $connec->userid)){//Si on change d'etudiant, on ecrit son nom
				if($currentStudent != 0){
					print_total();//On écrit les totaux de l'etudiant précédent
					$nbrequetestotal = 0;//RAZ des compteurs
					$dureetotale = 0;
					$nbrequetes += 4; 
				}
				$userinfo = get_etu_names($connec->userid);
				$worksheet1->write_string($nbrequetes, 0, noAccents(($userinfo->firstname)).' '.mb_strtoupper(noAccents(utf8_decode($userinfo->lastname))), $formattitre);
				//print_entete(true); L'appel de la fct est remplacé par son contenu. Sinon l'excel est mal formé. I-NEX-PLI-CABLE A corriger si possible
				$worksheet1->write_string($nbrequetes+1,0,get_string('debut', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,1,get_string('fin', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,2,get_string('nbrequete', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,3,get_string('duree', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,4,get_string('choixcat', 'block_connexion_tracking'),$formatentete);
			}
			$currentStudent = $connec->userid;
			print_ligne($connec, true);
		}
		print_total();//On ecrit le total du dernier étudiant.	
	}elseif(($form->etudiants == 0) && ($form->category != 0)){//Cas ou on liste une catégorie sur tous les étudiants
		$nbrequetestotal = 0;
		$dureetotale = 0;
		$nbrequetes = 0;
		$userinfo = get_etu_names($form->etudiants);
		$catname = get_cat_name($form->category);
		$worksheet1->write_string(0, 1, $catname, $formattitre);
		//HeaderingExcel("Tous-".filter($catname).$dates.'.xls');
		foreach ($connex as $connec) {
			if(($currentStudent == 0) || ($currentStudent != $connec->userid)){//Si on change d'etudiant, on ecrit son nom
				if($currentStudent != 0){
					print_total();//On écrit les totaux de l'etudiant précédent
					$nbrequetestotal = 0;//RAZ des compteurs
					$dureetotale = 0;
					$nbrequetes += 4; 
				}
				$userinfo = get_etu_names($connec->userid);
				
				//echo (mb_detect_encoding($userinfo->lastname)."<br>");
				if(mb_detect_encoding($userinfo->lastname) == "UTF-8"){
					echo '<br>';
					//echo mb_convert_encoding($userinfo->lastname, "Windows-1252", "UTF-8");
					echo changeAccented($userinfo->lastname);
				}
				
				
				//$worksheet1->write_string($nbrequetes, 0, noAccents(($userinfo->firstname)).' '.mb_convert_encoding($userinfo->lastname, "iso-8859-1", "UTF-8"), $formattitre);
				//$worksheet1->write_string($nbrequetes, 0, noAccents(($userinfo->firstname)).' '.excelCayLeMal($userinfo->lastname), $formattitre);
				//$worksheet1->write_string($nbrequetes, 0, noAccents(($userinfo->firstname)).' '.utf8_decode($userinfo->lastname), $formattitre);
				//$worksheet1->write_string($nbrequetes, 0, noAccents(($userinfo->firstname)).' '.$userinfo->lastname, $formattitre);
				//print_entete(false); L'appel de la fct est remplacé par son contenu. Sinon l'excel est mal formé. I-NEX-PLI-CABLE
				$worksheet1->write_string($nbrequetes+1,0,get_string('debut', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,1,get_string('fin', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,2,get_string('nbrequete', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,3,get_string('duree', 'block_connexion_tracking'),$formatentete);
				$worksheet1->write_string($nbrequetes+1,4,get_string('choixcat', 'block_connexion_tracking'),$formatentete);
			}
			$currentStudent = $connec->userid;
			print_ligne($connec, true);
		}
		print_total();//On ecrit le total du dernier étudiant.	
	}elseif(($form->category == 0) && ($form->etudiants != 0)){//Cas ou on liste toutes les connexions d'un etudiant, peu importe la catégorie.
		$nbrequetestotal = 0;
		$dureetotale = 0;
		$nbrequetes = 0;
		$userinfo = get_etu_names($form->etudiants);
		HeaderingExcel(filter($userinfo->firstname)."-".filter($userinfo->lastname)."-toutes-categories".$dates.".xls");
	
		$worksheet1->write_string($offset, 0, noAccents(($userinfo->firstname)).' '.mb_strtoupper(noAccents($userinfo->lastname)), $formattitre);
		
		print_entete(true);
		foreach ($connex as $connec) {
			print_ligne($connec, true);
		}
		print_total();	
	}else{//On liste les connexions d'un étudiant sur une catégories donnée.
		$nbrequetestotal = 0;
		$dureetotale = 0;
		$nbrequetes = 0;
		$userinfo = get_etu_names($form->etudiants);
		$catname = get_cat_name($form->category);
		HeaderingExcel(filter($userinfo->firstname)."-".filter($userinfo->lastname)."-".filter($catname).$dates.'.xls');
	
		$worksheet1->write_string(0, 0, noAccents(($userinfo->firstname)).' '.mb_strtoupper(noAccents($userinfo->lastname)), $formattitre);
		$worksheet1->write_string(0, 1, $catname, $formattitre);
		print_entete(true);
		foreach ($connex as $connec) {
			print_ligne($connec, true);
		}
		print_total();
	}
	
	//Fermeture du fichier -> téléchargement.
	$workbook->close();
	
}else{
	error("Droits insuffisants.");
}

function print_entete($printcat){
	global $worksheet1, $formatentete;
	$worksheet1->write_string($nbrequetes+1,0,get_string('debut', 'block_connexion_tracking'),$formatentete);
	$worksheet1->write_string($nbrequetes+1,1,get_string('fin', 'block_connexion_tracking'),$formatentete);
	$worksheet1->write_string($nbrequetes+1,2,get_string('nbrequete', 'block_connexion_tracking'),$formatentete);
	$worksheet1->write_string($nbrequetes+1,3,get_string('duree', 'block_connexion_tracking'),$formatentete);
	if($printcat)
		$worksheet1->write_string($nbrequetes+1,4,get_string('choixcat', 'block_connexion_tracking'),$formatentete);
}

function print_ligne($connec, $printcat){
	global $worksheet1, $formatentree, $nbrequetestotal, $dureetotale, $nbrequetes;
	$start = getdate($connec->start);
	$stop = getdate($connec->stop);
	//Formatage des données.
	$dateDebut = sprintf("%02d/%02d/%04d", $start[mday], $start[mon], $start[year]);
	$dateFin = sprintf("%02d/%02d/%04d", $stop[mday], $stop[mon], $stop[year]);
	$heureDebut = sprintf("%02dh%02dm%02ds", $start[hours], $start[minutes], $start[seconds]);
	$heureFin = sprintf("%02dh%02dm%02ds", $stop[hours], $stop[minutes], $stop[seconds]);
	$nbrequete = sprintf("%d", $connec->requests);
	$duree = getMinSec($connec->length);
	$duree = sprintf("%02dh%02dm%02ds", $duree[0], $duree[1], $duree[2]);
	//Ecriture.
	$worksheet1->write_string($nbrequetes+2, 0, $dateDebut." ".$heureDebut, $formatentree);
	$worksheet1->write_string($nbrequetes+2, 1, $dateFin." ".$heureFin, $formatentree);
	$worksheet1->write_string($nbrequetes+2, 2, $nbrequete, $formatentree);
	$worksheet1->write_string($nbrequetes+2, 3, $duree, $formatentree);
	if($printcat){
		$catname = (get_cat_name($connec->catid));
		$worksheet1->write_string($nbrequetes+2, 4, $catname, $formatentree);
		}
	$ligne ++;
	$nbrequetestotal += $connec->requests;
	$dureetotale += $connec->length;
	$nbrequetes++;
}

function print_total(){
	global $worksheet1, $formattitre, $nbrequetes, $nbrequetestotal, $dureetotale;
	$dureetotaleformaT = getMinSec($dureetotale);
	$dureetotaleformaT = sprintf("%02dh%02dm%02ds", $dureetotaleformaT[0], $dureetotaleformaT[1], $dureetotaleformaT[2]);
	$worksheet1->write_string($nbrequetes+2,0,'TOTAL:', $formattitre);
	$worksheet1->write_string($nbrequetes+2,2, $nbrequetestotal,$formattitre);
	$worksheet1->write_string($nbrequetes+2,3, $dureetotaleformaT,$formattitre);
}

//Header du fichier excel.
function HeaderingExcel($filename) {
	header("Content-type: application/vnd.ms-excel; charset=iso-8859-1");
	header("Content-Disposition: attachment; filename=$filename" );
	header("Expires: 0");
	header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
	header("Pragma: public");
}



 
 function changeAccented($text) {
 $replace = array(
 "Å " => "�", "Å¡" => "�", //"Å'" => "�", "Å\"" => "�",
 "Å¸" => "�", "Ã¿" => "ÿ", "Ã�" => "À", "Ã " => "à",
 "Ã�" => "Á", "Ã¡" => "á", "Ã�" => "Â", "Ã¢" => "â",
 "Ã�" => "Ã", "Ã£" => "ã", "Ã�" => "Ä", "Ã¤" => "ä",
 "Ã�" => "Å", "Ã¥" => "å", "Ã�" => "Æ", "Ã¦" => "æ",
 "Ã�" => "Ç", "Ã§" => "ç", "Ã�" => "È", "Ã¨" => "è",
 "Ã�" => "É", "Ã©" => "é", "Ã�" => "Ê", "Ãª" => "ê",
 "Ã�" => "Ë", "Ã«" => "ë", "Ã�" => "Ì", "Ã¬" => "ì",
 "Ã�" => "Í", "Ã­" => "í", "Ã�" => "Î", "Ã®" => "î",
 "Ã�" => "Ï", "Ã¯" => "ï", "Ã�" => "Ð", "Ã°" => "ð",
 "Ã'" => "Ñ", "Ã±" => "ñ", "Ã'" => "Ò", "Ã²" => "ò",
 "Ã"" => "Ó", "Ã³" => "ó", "Ã"" => "Ô", "Ã´" => "ô",
 "Ã�" => "Õ", "Ãµ" => "õ", "Ã�" => "Ö", "Ã�" => "Ø",
 "Ã¸" => "ø", "Ã�" => "Ù", "Ã¹" => "ù", "Ã�" => "Ú",
 "Ãº" => "ú", "Ã�" => "Û", "Ã»" => "û", "Ã�" => "Ü",
 "Ã¼" => "ü", "Ã�" => "Ý", "Ã½" => "ý", "Ã�" => "Þ",
 "Ã¾" => "þ", "Ã�" => "ß", "Ã¶" => "ö"
 );
 foreach($replace as $key => $val)
 $text = str_replace($key, $val, $text);
 return $text;
 } 

function noAccents(){
return $test = "hein ?";
}
?>